﻿using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ClosedXML;
using ClosedXML.Excel;
using System.IO;
using QuotingTool.DataModel;
using System.Globalization;

namespace QuotingTool.Utility
{
    public static class ExcelUtility
    {
        

        public static DataTable ReadAsDataTable(string fileName, int WorksheetNumber = 1)
        {
            DataTable dataTable = new DataTable();
            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
                IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                string relationshipId = sheets.Take(WorksheetNumber).Last().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
                Worksheet workSheet = worksheetPart.Worksheet;
                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();

                foreach (Cell cell in rows.ElementAt(0))
                {
                    dataTable.Columns.Add(GetCellValue(spreadSheetDocument, cell));
                }

                foreach (Row row in rows)
                {
                    DataRow dataRow = dataTable.NewRow();
                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                    }

                    dataTable.Rows.Add(dataRow);
                }

            }
            dataTable.Rows.RemoveAt(0);

            return dataTable;
        }



        private static string GetCellValue(SpreadsheetDocument document, Cell cell)
        {
            SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
            if (cell.CellValue != null)
            {
                string value = cell.CellValue.InnerXml;

                if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
                {
                    return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
                }
                else
                {
                    return value;
                }
            }
            else
                return String.Empty;
        }

        public static void ProcessSheet(string fileName, DataTable dataTable, int WorksheetNumber = 1)
        {
            var workbook = new XLWorkbook(fileName);
            var workSheet = workbook.Worksheet(WorksheetNumber);
            string workSheetName = workSheet.Name;
            workSheet.Delete();
            workbook.Worksheets.Add(dataTable, workSheetName);
            workbook.Worksheet(workSheetName).Position = WorksheetNumber;
            workbook.SaveAs(fileName);
        }

        public static void AddSumCell(string fileName, string columnName, int WorksheetNumber)
        {
            DataTable table = ReadAsDataTable(fileName, WorksheetNumber);
            if (table.Columns[columnName] != null)
            {
                int cellColumnIndex = table.Columns[columnName].Ordinal + 1;
                var workbook = new XLWorkbook(fileName);
                var workSheet = workbook.Worksheet(WorksheetNumber);
                int NumberOfLastRow = workSheet.LastRowUsed().RowNumber();
                IXLCell tempCell = workSheet.Cell(1, cellColumnIndex);
                IXLCell CellForNewData = workSheet.Cell(NumberOfLastRow + 1, cellColumnIndex);
                CellForNewData.SetValue("Sum");
                IXLCell CellForNewData1 = workSheet.Cell(NumberOfLastRow + 2, cellColumnIndex);
                var cellWithFormulaA1 = CellForNewData1;
                cellWithFormulaA1.FormulaA1 = "=SUM(" + tempCell.WorksheetColumn().ColumnLetter() + "2:" + tempCell.WorksheetColumn().ColumnLetter() + NumberOfLastRow + ")";
                for (int i = 2; i <= NumberOfLastRow; i++)
                {
                    workSheet.Cell(i, cellColumnIndex).SetDataType(XLCellValues.Number);
                }
                workSheet.Column(cellColumnIndex).AdjustToContents();
                workbook.CalculateMode = XLCalculateMode.Auto;
                workbook.SaveAs(fileName);
            }
        }

        public static void ReplacePlaceholders(string fileName, SummaryModel modelData, int WorksheetNumber = 1)
        {
            var workbook = new XLWorkbook(fileName);
            var workSheet = workbook.Worksheet(WorksheetNumber);
            CultureInfo ci = new CultureInfo("en-US");
            string sStartDate = string.Empty;
            string sEndDate = string.Empty;
            string DateApproved = string.Empty;

            if (!string.IsNullOrEmpty(modelData.SStartDate))
            {
                sStartDate = DateTime.Parse(modelData.SStartDate).ToString("dd MMM yyyy", ci);
            }

            if (!string.IsNullOrEmpty(modelData.SEndDate))
            {
                sEndDate = DateTime.Parse(modelData.SEndDate).ToString("dd MMM yyyy", ci);
            }

            if (!string.IsNullOrEmpty(modelData.DateApproved))
            {
                DateApproved = DateTime.Parse(modelData.DateApproved).ToString("dd MMM yyyy", ci);
            }

            IXLCells cellsUsed = workSheet.CellsUsed();
            foreach (IXLCell cell in cellsUsed)
            {
                string cellValue = cell.Value.ToString();
                if (cellValue.Contains("<CompanyName>"))
                {
                    cell.Value = cellValue.Replace("<CompanyName>", modelData.EndCustomerName);
                    continue;
                }
                switch (cellValue.Trim())
                {

                    case "<Sdescription>":
                        cell.Value = modelData.SDescription;
                        break;
                    case "<SStartDate>":
                        cell.Value = sStartDate;
                        break;
                    case "<SEndDate>":
                        cell.Value = sEndDate;
                        break;
                    case "<SMaintPriceIncCol>":
                        cell.Value = modelData.SProratedMaintPriceInclCOL;
                        break;
                    case "<SOneLineDiscount>":
                        cell.Value = modelData.SOneLineDiscount;
                        break;
                    case "<SPPrePaidMultiYearDiscount>":
                        cell.Value = modelData.SPrePaidMultiDiscount;
                        break;
                    case "<SAnnualMaintPriceInclCol>":
                        cell.Value = modelData.SAnnualMaintPriceInclCOL;
                        break;
                    case "<SCurrency>":
                        cell.Value = modelData.SCurrency;
                        break;
                    case "<EndCustomerName>":
                        cell.Value = modelData.EndCustomerName;
                        break;
                    case "<PartnerName>":
                        cell.Value = modelData.PartnerName;
                        break;
                    case "<QuoteNumber>":
                        cell.Value = modelData.QuoteNumber;
                        break;
                    case "<QuoteDate>":
                        cell.Value = DateApproved;
                        break;
                    case "<QuoteOwner>":
                        cell.Value = modelData.QuoteOwner;
                        break;


                    //case "<LeadContractNumber>":
                    //    cell.Value = modelData.LeadContractNumber;
                    //    break;
                    //case "<ContractNumberModifier>":
                    //    cell.Value = modelData.ContractNumberModifier;
                    //    break;
                }
            }
            int LastColumnIndex = workSheet.LastColumnUsed().ColumnNumber();
            int LastRowIndex = workSheet.LastRowUsed().RowNumber() + 1;
            for (int i = LastColumnIndex + 1; i < LastColumnIndex + 50; i++)
            {
                for (int xi = 1; xi <= LastRowIndex; xi++)
                {
                    workSheet.Cell(xi, i).Style.Fill.SetBackgroundColor(XLColor.White);
                }
            }
            workbook.Save();
        }
    
    }
}